package com.song.util.excel;

import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.util.PoitlIOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * easy-poi工具类
 */
public class EasyPoiUtil {
    public static void exportExcel(ExportParams exportParams, Class<?> pojoClass, Collection<?> dataSet, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = createWorkbook(exportParams.getType());
        new CustomExcelExportService().createSheet(workbook, exportParams, pojoClass, dataSet);
        downLoadExcel(fileName, response, workbook);
    }

    public static void exportExcel(List<Map<String, Object>> list, ExcelType type, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = createWorkbook(type);
        Iterator var3 = list.iterator();
        while(var3.hasNext()) {
            Map<String, Object> map = (Map)var3.next();
            CustomExcelExportService service = new CustomExcelExportService();
            ExportParams params = (ExportParams)map.get("title");
            params.setType(type);
            service.createSheet(workbook, params, (Class)map.get("entity"), (Collection)map.get("data"));
        }
        downLoadExcel(fileName, response, workbook);
    }

    public static void exportExcel(ExportParams exportParams, List<ExcelExportEntity> entityList, Collection<?> dataSet, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = createWorkbook(exportParams.getType());
        new CustomExcelExportService().createSheetForMap(workbook, exportParams, entityList, dataSet);
        downLoadExcel(fileName, response, workbook);
    }

    public static void exportExcel(TemplateExportParams params, Map<String, Object> map, boolean needMerge, List<SheetMergeParam> sheetMergeParamList, String fileName, HttpServletResponse response) throws IOException {
        if (params == null){
            throw new RuntimeException("导出模板为空");
        }
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        if (needMerge){
            mergeExcel(workbook, sheetMergeParamList);
        }
        downLoadExcel(fileName, response, workbook);
    }

    public static ExportParams createExportParams(String title, String secondTitle, String sheetName, ExcelType excelType){
        ExportParams exportParams = new ExportParams(title, secondTitle, sheetName);
        exportParams.setType(excelType);
        return exportParams;
    }

    public static Workbook createWorkbook(ExcelType excelType){
        return ExcelType.HSSF.equals(excelType) ? new HSSFWorkbook() : new XSSFWorkbook();
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setHeader("Pragma", "No-cache");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    public static void downloadWord(String fileName, HttpServletResponse response,  XWPFTemplate xwpfTemplate) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        OutputStream out = response.getOutputStream();
        BufferedOutputStream bos = new BufferedOutputStream(out);
        xwpfTemplate.write(bos);
        bos.flush();
        out.flush();
        PoitlIOUtils.closeQuietlyMulti(xwpfTemplate, bos, out);
    }

    public static <T> List<T> importExcel(File file, Class<T> pojoClass, ImportParams params){
        return ExcelImportUtil.importExcel(file, pojoClass, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass, ImportParams params) throws IOException {
        if (file == null){
            return null;
        }
        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        }catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    public static ImportParams createImportParams(int titleRows, int headRows, int sheetNum){
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(titleRows);
        importParams.setHeadRows(headRows);
        importParams.setSheetNum(sheetNum);
        return importParams;
    }

    private static void mergeExcel(Workbook workbook, List<SheetMergeParam> sheetMergeParamList) {
        for (SheetMergeParam sheetMergeParam : sheetMergeParamList) {
            Sheet sheet = workbook.getSheetAt(sheetMergeParam.getSheetIndex());
            int lastRowNum = sheet.getLastRowNum();
            int i;
            for (i = 0; i < lastRowNum; i++) {
                //获取每行第一个单元格
                if (null == sheet.getRow(i) || null == sheet.getRow(i).getCell(0)) {
                    continue;
                }
                Cell cell = sheet.getRow(i).getCell(0);
                if (sheetMergeParam.getIgnoreCellValues().contains(cell.getStringCellValue()) || StringUtils.isEmpty(cell.getStringCellValue())) {
                    continue;
                }
                //定义合并终止行数
                int endRowNum = 0;
                for (int j = i + 1; j <= lastRowNum; j++) {
                    Cell desColumn = sheet.getRow(i).getCell(sheetMergeParam.getDesColumnIndex());
                    Cell nextDesColumn = sheet.getRow(j).getCell(sheetMergeParam.getDesColumnIndex());
                    if (!desColumn.getStringCellValue().equals(nextDesColumn.getStringCellValue())) {
                        //值不同，终止此层循环
                        break;
                    }
                    endRowNum++;
                }

                //判断是否有合并项
                if (endRowNum == 0) {
                    continue;
                }
                //合并单元格操作
                for (int z = 0; z < sheetMergeParam.getMergeColumnIndexs().length; z++) {
                    //合并起始行，终止行，起始列，终止列
                    int firstRow = i;
                    int lastRow = i + endRowNum;
                    int firstCol = sheetMergeParam.getMergeColumnIndexs()[z];
                    int lastCol = sheetMergeParam.getMergeColumnIndexs()[z];
                    PoiMergeCellUtil.addMergedRegion(sheet, firstRow, lastRow, firstCol, lastCol);
                }
                //合并后行号下移
                i = i + endRowNum;
            }
        }
    }

    /**
     * 动态导出
     * @param list  数据列表
     * @param fields    导出的字段
     * @param filename  文件名
     * @param exportParams  导出参数（标题、sheet名称、是否创建表头，表格类型）
     * @param source    数据源
     * @param response
     * @throws IOException
     */
    public static void exportExcel(List<Map<String, Object>> list, List<String> fields, String filename, ExportParams exportParams, String source, HttpServletResponse response) throws IOException {
        //获取标题栏
        List<ExcelExportEntity> headers = new ArrayList<>();
        for (String field : fields) {
            ExcelExportEntity excelExportEntity = new ExcelExportEntity(getHeader(source, field), field);
            headers.add(excelExportEntity);
        }
        exportExcel(list, headers, filename, exportParams, response);
    }

    /**
     * 动态导出
     * @param list  数据列表
     * @param headers   标题头
     * @param filename  文件名
     * @param exportParams  导出参数（标题、sheet名称、是否创建表头，表格类型）
     * @param response
     * @throws IOException
     */
    public static void exportExcel(List<Map<String, Object>> list, List<ExcelExportEntity> headers, String filename, ExportParams exportParams, HttpServletResponse response) throws IOException {
        Workbook workbook = exportExcel(exportParams, headers, list);
        downLoadExcel(filename, response, workbook);
    }

    /**
     * 多个sheet导出
     * @param sheets
     * @return
     * @throws Exception
     */
    public static Workbook exportManySheets(List<Map<String, Object>> sheets) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        for (Map<String, Object> m : sheets) {
            CustomExcelExportService service = new CustomExcelExportService();
            ExportParams param = (ExportParams) m.get(NormalExcelConstants.PARAMS);
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) m.get(NormalExcelConstants.MAP_LIST);
            Collection<?> data = (Collection<?>) m.get(NormalExcelConstants.DATA_LIST);
            service.createSheetForMap(workbook, param, entity, data);
        }
        return workbook;
    }

    public static Workbook exportExcel(ExportParams params, List<ExcelExportEntity> mapList, Collection<?> data) {
        Workbook workbook = createWorkbook(params.getType());
        CustomExcelExportService service = new CustomExcelExportService();
        service.createSheetForMap(workbook, params, mapList, data);
        return workbook;
    }

    public static String getHeader(String source, String field) {
        switch (source) {
            case "USER":
                return "";
            default:
                return field;
        }
    }

    /**
     * 设置单元格最大字符数
     * 不建议使用
     */
    public static void initCellMaxTextLength(){
        SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
        if (Integer.MAX_VALUE != excel2007.getMaxTextLength()){
            Field field;
            try {
                field = excel2007.getClass().getDeclaredField("_maxTextLength");
                field.setAccessible(true);
                field.set(excel2007, Integer.MAX_VALUE);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 解析Excel表格中的图片信息
     * @param file
     * @return
     */
    public static Map<String, Map<String, XSSFPictureData>> parsePictures(MultipartFile file){
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            return parsePictures(workbook, 0);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static Map<String, Map<String, XSSFPictureData>> parsePictures(XSSFWorkbook workbook, int sheetIndex){
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        return parsePictures(sheet);
    }

    public static Map<String, Map<String, XSSFPictureData>> parsePictures(XSSFSheet sheet){
        Map<String, Map<String, XSSFPictureData>> rowMap = new HashMap<>();
        List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes();
        for (XSSFShape shape : shapes){
            if (shape instanceof XSSFPicture){
                XSSFPicture picture = (XSSFPicture) shape;
                //获取锚点
                XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor) picture.getAnchor();
                XSSFPictureData pictureData = picture.getPictureData();
                //获取行列信息
                String row = String.valueOf(xssfClientAnchor.getRow1());
                String col = String.valueOf(xssfClientAnchor.getCol1());
                if (rowMap.containsKey(row)){
                    Map<String, XSSFPictureData> colMap = rowMap.get(row);
                    colMap.put(col, pictureData);
                }else {
                    Map<String, XSSFPictureData> colMap = new HashMap<>();
                    colMap.put(col, pictureData);
                    rowMap.put(row, colMap);
                }
            }
        }
        return rowMap;
    }
}
